var mysql = require('mysql');
const Q = require('q');
const fs = require("fs");
const deffer = Q.defer();
var connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'root',
    database: 'v7'
});

connection.on('error', function(err) {
    throw err;
});

connection.connect();


// connection.query("SELECT tap.id , concat(p. NAME , ',' , c. NAME , ',' , cc. NAME) AS areaNames , concat(p.id , ',' , c.id , ',' , cc.id) AS areaIds FROM t_agr_prod_geo_indicat tap LEFT JOIN( SELECT province.* FROM area province WHERE province.levels = 1) p ON( LOCATE(p. NAME , tap.area_names) = 1) LEFT JOIN( SELECT city.* FROM area city WHERE city.levels = 2) c ON( LOCATE(c. NAME , tap.area_names) > 0 AND c.parent_id = p.id) LEFT JOIN( SELECT county.* FROM area county WHERE county.levels = 3) cc ON( LOCATE(cc.NAME, tap.area_names) > 0 AND c.parent_id = p.id )", function(error, results, fields) {
//     if (error) throw error;
//     // console.log(results);
//     var sqlStr = "";
//     for (var key in results) {
//         var ele = results[key];
//         if (ele.areaIds != null && ele.areaNames != null) {
//             let sql = 'UPDATE t_agr_prod_geo_indicat SET area_ids = "' +
//                 ele.areaIds.trim() +
//                 '", areaNames = "' +
//                 ele.areaNames.trim() +
//                 '" WHERE id = ' +
//                 ele.id + ';\n';

//             sqlStr += sql;
//         }
//     }

//     fs.writeFile('message.sql', sqlStr, 'utf8', (error) => {

//         console.log(error);
//     });


// });

//生成地理标识的insert语句
connection.query("select * from t_agr_prod_geo_indicat where area_names like '%山东省%'", (error, results) => {

    if (error) { console.error(error); return; }
    var sqlStr = "";
    results.forEach(function(element) {

        sqlStr += `insert into t_pinjian_area_common_brand(area_ids,area_names,check_status,create_date,first_level,info,logo,name,publisher_id,second_level,type,update_date) 
        values
        ('${element.area_ids}','${element.areaNames}',2,now(),'${element.Kind2_id}','${element.info}','${element.logo}','${element.name}',2613,'${element.Kind1_id}',2,now());\n`;
    }, this);

    fs.writeFile('insertDili.sql', sqlStr, 'utf-8', (error) => {

        console.log(error);
    });
});
connection.end();